package com.samson.empapp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;

import com.samson.empapp.dbo.RegionsDbo;
import com.samson.empapp.util.DbUtil;

public class RegionsDao implements IRegionsDao{
	static Logger logger = Logger.getLogger(RegionsDao.class);

	@Override
	public RegionsDbo findById(int regionId) {
		RegionsDbo dbo = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			dbo= new RegionsDbo();
			sb = new StringBuilder();
			sb.append("Select "); 
			sb.append("REGION_ID as regionId, REGION_NAME as regionName "); 
			sb.append("from REGIONS "); 
			sb.append("where REGION_ID = "+regionId+" ");
			pstmt = con.prepareStatement(sb.toString());
			
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
			 dbo.setRegionId(regionId);
			 dbo.setRegionName(rs.getString("regionName"));
			 
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		return dbo;
	}

	@Override
	public List<RegionsDbo> findAll() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void insert(RegionsDbo dbo) throws Exception {
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("INSERT INTO \"HR\".\"REGIONS\" "); 
			sb.append("(REGION_ID,REGION_NAME) VALUES "); 
			sb.append("(?,?) ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			pstmt.setInt(1, dbo.getRegionId());
			pstmt.setString(2,dbo.getRegionName());			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Exception" + e);
			throw e;
		} finally {
			DbUtil.closeDBConnection(pstmt, con, sb);
		}
		
	}

	@Override
	public void update(RegionsDbo user) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void delete(int id) {
		// TODO Auto-generated method stub
		
	}

}
